using System;
using System.Data;
using System.Data.Common;
using Avanzis.QueryExecutor;
using NUnit.Framework;

namespace Avanzis.QueryObjects.Tests
{
    [TestFixture]
    public class QueryObjectSessionTest : BaseTest
    {
        [TestFixtureSetUp()]
        public void SetUp()
        {
            ExecuteNonQuery("DELETE FROM REFEREES");
        }

        [Test]
        public void TestExternalOpenedConnection()
        {
            DbConnection connection = Database.CreateConnection(this.connectionString);

            connection.Open();

            using (Session session = new Session(connection))
            {
                session.Open();

                Referee r1 = new Referee(session);
                r1.Code = "XXX";
                r1.Name = "Referee XXX";
                r1.Save();
                
                Referee r2 = new Referee(session);
                r2.Code = "XXX";
                r2.Name = "Referre 2";
                r2.Save();                               
                
                int refereeCount = Convert.ToInt32(ExecuteScalar("select count(*) from referees where code = 'XXX'"));

                Assert.AreEqual(1, refereeCount);

                Assert.AreSame(connection, session.Connection);

                ExecuteNonQuery("DELETE FROM REFEREES");

                session.Close();
            }

            Assert.AreEqual(ConnectionState.Open, connection.State);

            connection.Close();    
            
        }

        [Test]
        public void TestExternalClosedConnection()
        {
            DbConnection connection = Database.CreateConnection(this.connectionString);            

            using (Session session = new Session(connection))
            {
                session.Open();

                Referee r1 = new Referee(session);
                r1.Code = "XXX";
                r1.Name = "Referee XXX";
                r1.Save();

                Referee r2 = new Referee(session);
                r2.Code = "XXX";
                r2.Name = "Referre 2";
                r2.Save();

                int refereeCount = Convert.ToInt32(ExecuteScalar("select count(*) from referees where code = 'XXX'"));

                Assert.AreEqual(1, refereeCount);

                Assert.AreSame(connection, session.Connection);

                ExecuteNonQuery("DELETE FROM REFEREES");

                session.Close();
            }

            Assert.AreEqual(ConnectionState.Closed, connection.State);           

        }
        [Test]
        public void TestSimpleSession()
        {
            SessionFactory sessionFactory = new SessionFactory();            

            using (Session session = new Session())
            {
                session.Open();

                Referee r1 = new Referee(session);
                r1.Code = "XXX";
                r1.Name = "Referee XXX";
                r1.Save();

                // 
                int refereeCount = Convert.ToInt32(ExecuteScalar("select count(*) from referees where code = 'XXX'"));
                Assert.AreEqual(1, refereeCount);

                Referee r2 = new Referee(session);
                r2.Code = "XXX";
                r2.Name = "Referre 2";
                r2.Save();

                session.Close();
            }
        }

        [Test]
        public void TestSimpleTransactionRollBack()
        {
            ExecuteNonQuery("DELETE FROM REFEREES WHERE CODE = 'XXX'");

            using (Session session = new Session())
            {
                session.Open();

                session.BeginTransaction();

                Referee r1 = new Referee(session);
                r1.Code = "XXX";
                r1.Name = "Referee XXX";
                r1.Save();                

                session.RollBack();

                session.Close();
            }

            int refereeCount = Convert.ToInt32(ExecuteScalar("select count(*) from referees where code = 'XXX'"));
            Assert.AreEqual(0, refereeCount);
        }

        [Test]
        public void TestSimpleTransactionCommit()
        {
            ExecuteNonQuery("DELETE FROM REFEREES WHERE CODE = 'XXX'");

            using (Session session = new Session())
            {
                session.Open();

                session.BeginTransaction();

                Referee r1 = new Referee(session);
                r1.Code = "XXX";
                r1.Name = "Referee XXX";
                r1.Save();                

                session.Commit();
            }

            int refereeCount = Convert.ToInt32(ExecuteScalar("select count(*) from referees where code = 'XXX'"));
            Assert.AreEqual(1, refereeCount);

            ExecuteNonQuery("DELETE FROM REFEREES WHERE CODE = 'XXX'");
        }
    }
}
